/**
 * 余额数据库管理模块
 *
 * 本文件负责管理余额相关的数据库操作，包括：
 * - 余额表的初始化和维护
 * - 余额记录的增删改查操作
 * - 数据库记录数量控制（超过10000条自动清理）
 * - 余额数据的统计和查询
 *
 * 数据库表结构：
 * - 表名：balance_data
 * - 字段：id(主键), balance(余额), addtime(添加时间), status(状态), created_at(创建时间)
 *
 * 作者：系统生成
 * 创建时间：2024
 */

import getAstsdk, { type SQLitePlugin } from "ast_sdk";
import balanceBaseUtils from "../../utils/astUtils/balanceBaseUtils/balanceBaseUtils";
import { IBalanceData } from "./types";

/**
 * 余额数据库管理类
 * 采用单例模式，确保全局只有一个实例
 */
export class DeviceKeyValueDatabase {
  private static instance: DeviceKeyValueDatabase;
  private deviceKeyValueDB!: SQLitePlugin.Database;
  private sqlitePlugin!: SQLitePlugin.SQLite;

  private constructor() {
    const astsdk = getAstsdk();
    if (!astsdk) {
      console.warn("AST SDK 未初始化");
      return;
      // throw new Error('AST SDK 未初始化')
    }
    this.sqlitePlugin = astsdk.getSqlite();
  }

  /**
   * 获取单例实例
   * @returns DeviceKeyValueDatabase 实例
   */
  public static getInstance(): DeviceKeyValueDatabase {
    if (!DeviceKeyValueDatabase.instance) {
      DeviceKeyValueDatabase.instance = new DeviceKeyValueDatabase();
    }
    return DeviceKeyValueDatabase.instance;
  }

  /**
   * 初始化并检查余额表
   * 如果记录数超过10000条，会自动删除数据库重新创建
   */
  public async initAndCheckDeviceKeyValueTable(): Promise<void> {
    try {
      // 打开数据库连接
      this.deviceKeyValueDB = this.sqlitePlugin.openDatabase({
        name: "lynn_key_value.db",
        location: "default",
        androidDatabaseProvider: "system",
      });

      // 初始化表结构
      await this.initDeviceKeyValueTable();
    } catch (error) {
      throw error;
    }
  }

  /**
   * 初始化设备键值表
   * 创建 wl7_key_value 表，包含键值对
   */
  public initDeviceKeyValueTable(): Promise<void> {
    return new Promise((resolve, reject) => {
      const createTable = `
        CREATE TABLE IF NOT EXISTS freezer_key_value (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          key TEXT,
          value TEXT,
          addtime TEXT,
          created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
      `;

      this.deviceKeyValueDB.transaction(
        (tx: SQLitePlugin.Transaction) => {
          tx.executeSql(createTable);
        },
        (error: Error) => {
          console.error("余额表初始化 ERROR: " + error.message);
          reject(error);
        },
        () => {
          console.log("余额表初始化成功");
          resolve();
        }
      );
    });
  }

  /**
   * 获取余额记录总数
   * @returns Promise<number> 记录总数
   */
  private getBalanceCount(): Promise<number> {
    return new Promise((resolve, reject) => {
      this.deviceKeyValueDB.transaction(
        (tx: SQLitePlugin.Transaction) => {
          tx.executeSql(
            "SELECT COUNT(*) as count FROM balance_data",
            [],
            (tx: SQLitePlugin.Transaction, results: SQLitePlugin.Results) => {
              const count = results.rows.item(0).count;
              resolve(count);
            },
            (tx: SQLitePlugin.Transaction, error: Error) => {
              reject(error);
              return false;
            }
          );
        },
        (error: Error) => {
          reject(error);
        }
      );
    });
  }

  /**
   * 删除整个余额数据库
   * 用于清理过多的历史记录
   */
  private deleteBalanceDatabase(): Promise<void> {
    return new Promise((resolve, reject) => {
      this.sqlitePlugin.deleteDatabase(
        {
          name: "my.balance",
          location: "default",
        },
        () => {
          console.log("余额数据库删除成功");
          resolve();
        },
        (error: Error) => {
          console.error("删除余额数据库时出错:", error);
          reject(error);
        }
      );
    });
  }

  /**
   * 将SQLite查询结果转换为数组
   * @param rs SQLite查询结果
   * @returns IBalanceData[] 结果数组
   */
  private rsToArray(rs: SQLitePlugin.Results): IBalanceData[] {
    const resultArr: IBalanceData[] = [];
    for (let i = 0; i < rs.rows.length; i++) {
      resultArr.push(rs.rows.item(i));
    }
    return resultArr;
  }

  /**
   * 设置键值对数据（类似localStorage.setItem）
   * @param key 键名
   * @param value 值
   * @returns Promise<void>
   */
  public setItem(key: string, value: string): Promise<void> {
    return new Promise((resolve, reject) => {
      const addtime = balanceBaseUtils.getTime().toString();

      this.deviceKeyValueDB.transaction(
        (tx: SQLitePlugin.Transaction) => {
          // 先检查是否已存在该key
          tx.executeSql(
            "SELECT id FROM freezer_key_value WHERE key = ?",
            [key],
            (tx: SQLitePlugin.Transaction, results: SQLitePlugin.Results) => {
              if (results.rows.length > 0) {
                // 更新现有记录
                tx.executeSql(
                  "UPDATE freezer_key_value SET value = ?, addtime = ? WHERE key = ?",
                  [value, addtime, key]
                );
              } else {
                // 插入新记录
                tx.executeSql(
                  "INSERT INTO freezer_key_value (key, value, addtime) VALUES (?, ?, ?)",
                  [key, value, addtime]
                );
              }
            },
            (tx: SQLitePlugin.Transaction, error: Error) => {
              reject(error);
              return false;
            }
          );
        },
        (error: Error) => {
          console.error("设置键值对时出错:", error);
          reject(error);
        },
        () => {
          resolve();
        }
      );
    });
  }

  /**
   * 获取键值对数据（类似localStorage.getItem）
   * @param key 键名
   * @returns Promise<string | null> 返回值，如果不存在则返回null
   */
  public getItem(key: string): Promise<string | null> {
    return new Promise((resolve, reject) => {
      this.deviceKeyValueDB.transaction(
        (tx: SQLitePlugin.Transaction) => {
          tx.executeSql(
            "SELECT value FROM freezer_key_value WHERE key = ?",
            [key],
            (tx: SQLitePlugin.Transaction, results: SQLitePlugin.Results) => {
              if (results.rows.length > 0) {
                resolve(results.rows.item(0).value);
              } else {
                resolve(null);
              }
            },
            (tx: SQLitePlugin.Transaction, error: Error) => {
              reject(error);
              return false;
            }
          );
        },
        (error: Error) => {
          console.error("获取键值对时出错:", error);
          reject(error);
        }
      );
    });
  }

  /**
   * 删除指定键的数据（类似localStorage.removeItem）
   * @param key 键名
   * @returns Promise<void>
   */
  public removeItem(key: string): Promise<void> {
    return new Promise((resolve, reject) => {
      this.deviceKeyValueDB.transaction(
        (tx: SQLitePlugin.Transaction) => {
          tx.executeSql(
            "DELETE FROM freezer_key_value WHERE key = ?",
            [key],
            (tx: SQLitePlugin.Transaction, results: SQLitePlugin.Results) => {
              resolve();
            },
            (tx: SQLitePlugin.Transaction, error: Error) => {
              reject(error);
              return false;
            }
          );
        },
        (error: Error) => {
          console.error("删除键值对时出错:", error);
          reject(error);
        },
        () => {
          resolve();
        }
      );
    });
  }

  /**
   * 清空所有键值对数据（类似localStorage.clear）
   * @returns Promise<void>
   */
  public clear(): Promise<void> {
    return new Promise((resolve, reject) => {
      this.deviceKeyValueDB.transaction(
        (tx: SQLitePlugin.Transaction) => {
          tx.executeSql(
            "DELETE FROM freezer_key_value",
            [],
            (tx: SQLitePlugin.Transaction, results: SQLitePlugin.Results) => {
              resolve();
            },
            (tx: SQLitePlugin.Transaction, error: Error) => {
              reject(error);
              return false;
            }
          );
        },
        (error: Error) => {
          console.error("清空键值对时出错:", error);
          reject(error);
        },
        () => {
          resolve();
        }
      );
    });
  }

  /**
   * 获取所有键名（类似localStorage.key）
   * @returns Promise<string[]> 所有键名数组
   */
  public getAllKeys(): Promise<string[]> {
    return new Promise((resolve, reject) => {
      this.deviceKeyValueDB.transaction(
        (tx: SQLitePlugin.Transaction) => {
          tx.executeSql(
            "SELECT key FROM freezer_key_value ORDER BY created_at",
            [],
            (tx: SQLitePlugin.Transaction, results: SQLitePlugin.Results) => {
              const keys: string[] = [];
              for (let i = 0; i < results.rows.length; i++) {
                keys.push(results.rows.item(i).key);
              }
              resolve(keys);
            },
            (tx: SQLitePlugin.Transaction, error: Error) => {
              reject(error);
              return false;
            }
          );
        },
        (error: Error) => {
          console.error("获取所有键名时出错:", error);
          reject(error);
        }
      );
    });
  }

  /**
   * 获取键值对数量（类似localStorage.length）
   * @returns Promise<number> 键值对数量
   */
  public getLength(): Promise<number> {
    return new Promise((resolve, reject) => {
      this.deviceKeyValueDB.transaction(
        (tx: SQLitePlugin.Transaction) => {
          tx.executeSql(
            "SELECT COUNT(*) as count FROM freezer_key_value",
            [],
            (tx: SQLitePlugin.Transaction, results: SQLitePlugin.Results) => {
              const count = results.rows.item(0).count;
              resolve(count);
            },
            (tx: SQLitePlugin.Transaction, error: Error) => {
              reject(error);
              return false;
            }
          );
        },
        (error: Error) => {
          console.error("获取键值对数量时出错:", error);
          reject(error);
        }
      );
    });
  }

  /**
   * 检查指定键是否存在
   * @param key 键名
   * @returns Promise<boolean> 是否存在
   */
  public hasKey(key: string): Promise<boolean> {
    return new Promise((resolve, reject) => {
      this.deviceKeyValueDB.transaction(
        (tx: SQLitePlugin.Transaction) => {
          tx.executeSql(
            "SELECT COUNT(*) as count FROM freezer_key_value WHERE key = ?",
            [key],
            (tx: SQLitePlugin.Transaction, results: SQLitePlugin.Results) => {
              const count = results.rows.item(0).count;
              resolve(count > 0);
            },
            (tx: SQLitePlugin.Transaction, error: Error) => {
              reject(error);
              return false;
            }
          );
        },
        (error: Error) => {
          console.error("检查键是否存在时出错:", error);
          reject(error);
        }
      );
    });
  }

  /**
   * 获取所有键值对数据
   * @returns Promise<{[key: string]: string}> 所有键值对对象
   */
  public getAllItems(): Promise<{ [key: string]: string }> {
    return new Promise((resolve, reject) => {
      this.deviceKeyValueDB.transaction(
        (tx: SQLitePlugin.Transaction) => {
          tx.executeSql(
            "SELECT key, value FROM freezer_key_value ORDER BY created_at",
            [],
            (tx: SQLitePlugin.Transaction, results: SQLitePlugin.Results) => {
              const items: { [key: string]: string } = {};
              for (let i = 0; i < results.rows.length; i++) {
                const row = results.rows.item(i);
                items[row.key] = row.value;
              }
              resolve(items);
            },
            (tx: SQLitePlugin.Transaction, error: Error) => {
              reject(error);
              return false;
            }
          );
        },
        (error: Error) => {
          console.error("获取所有键值对时出错:", error);
          reject(error);
        }
      );
    });
  }
}

// 导出单例实例
export default DeviceKeyValueDatabase.getInstance();
